package cn.youframe.sample.db;

import cn.youframe.frame.base.BaseCtrl;
import cn.youframe.frame.beans.AjaxVo;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import java.util.List;
import java.util.Map;

/**
 * User: Administrator
 * DateTime: 12-7-22 下午2:17
 * Desc:
 */
@Controller
@RequestMapping(value = "/sample")
public class DBManagerCtrl extends BaseCtrl {
    @Autowired
    DBManagerService dbManagerService;

    /**
     * 获取所有表
     * @param request
     * @return
     */
    @RequestMapping(value = "/DBManagerCtrl!initTab.do")
    public String initTab(HttpServletRequest request){
        logger.info("DBManagerCtrl:initTab");
        request.setAttribute("tables",dbManagerService.getTables());
        return "sample/db/DBManagerLeft";
    }

    /**
     * 执行Sql语句
     * @param request
     * @return
     */
    @RequestMapping(value = "/DBManagerCtrl!execute.do")
    public String execute(HttpServletRequest request){
        logger.info("DBManagerCtrl:execute");
        String execSql=request.getParameter("execSql");
        boolean ifselect=true;
        String execType=request.getParameter("execType");
        logger.info("execType="+execType+",execSql="+execSql);
        String sqlType=null;
        if(execSql!=null){
            execSql=execSql.trim();
            if(!execType.equalsIgnoreCase("0")){
                ifselect=true;
            }else{
                if(StringUtils.ordinalIndexOf(execSql," ",1)!=-1){
                    sqlType=execSql.substring(0,StringUtils.ordinalIndexOf(execSql," ",1));
                }
                ifselect=sqlType.toUpperCase().startsWith("SELECT");
            }
        }
        if(ifselect){//查询类
            List<Map<String,Object>> rList = dbManagerService.query(execSql,execType);
            request.setAttribute("results",rList);
        }else{//更新类
            int updateNum=dbManagerService.execUpdate(execSql);
            request.setAttribute("updateNum",updateNum);
        }
        request.setAttribute("ifselect", String.valueOf(ifselect));
        return "sample/db/DbQuery";
    }

    /**
     * 生成Sql语句
     * @param tableName
     * @param sqlType
     * @return
     */
    @RequestMapping(value = "/DBManagerCtrl!genSql.do")
    public @ResponseBody AjaxVo genSql(@RequestParam String tableName,@RequestParam String sqlType){
        AjaxVo vo=new AjaxVo();
        try{
            List<Map<String,Object>> rList = dbManagerService.query(tableName,"1");
            StringBuffer sqlbuf=new StringBuffer();
            if(rList!=null && rList.size()>0){
                Map<String,Object> colmap=null;
                int colsize=rList.size();
                for (int i=0;i<colsize;i++){
                    colmap=rList.get(i);
                    if(sqlType.equals("select")){//SELECT语句
                        if(i==0)
                            sqlbuf.append("SELECT ");
                        if(i==(colsize-1)){
                            sqlbuf.append(colmap.get("COLUMN_NAME"));
                            sqlbuf.append(" FROM ").append(tableName);
                        }else
                            sqlbuf.append(colmap.get("COLUMN_NAME")+",");
                    }else {//UPDATE语句
                        if(i==0)
                            sqlbuf.append("UPDATE ").append(tableName).append(" SET ");
                        if(i==(colsize-1)){
                            sqlbuf.append(colmap.get("COLUMN_NAME")).append("=? ");
                            sqlbuf.append(" WHERE ");
                        }else
                            sqlbuf.append(colmap.get("COLUMN_NAME")).append("=?,");
                    }
                }
                vo.setResObj(sqlbuf.toString());
            }
        }catch (Exception ex){
            vo.setFlag(false);
            vo.setMessage(ex.getMessage());
            logger.error(ex.getMessage(),ex);
        }
        return vo;
    }
}
